SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE spUFGMasterFieldsMetaQuery
--This SP gets all metadata of columns of a certain table of a certain user
--It takes into account the rolesettings, role-rights and user-savings of open-panels
@Tablename varchar(128),
@Language varchar(100),
@username varchar(50)
WITH ENCRYPTION AS
--IF EXISTS(SELECT 1 FROM tblUFGUsers HAVING COUNT(*) > 5) BEGIN RAISERROR('The current IMAGine version licenced to HAN University of Applied Sciences is limited to a maximum of 5 users.',17,1) RETURN END
--This SP gets all metadata of columns of a certain table of a certain user
--It takes into account the rolesettings, role-rights and user-savings of open-panels
SET NOCOUNT ON
declare @isadmin bit = CASE WHEN EXISTS (SELECT 1 FROM tblUFGUsersInRoles WHERE ((uir_UserName = @Username) and (uir_RoleName = 'UFGAdministrator'))) THEN 1 ELSE 0 END
declare @languagedependent int
SELECT @languagedependent = MIN(CAST(urr_LanguageDependent AS int)) FROM tblUFGRoles INNER JOIN tblUFGUsersInRoles ON urr_RoleName = uir_RoleName and uir_UserName = @UserName WHERE uir_RoleIsPlayed=1

SELECT
  uuq_PrimaryKey= case when EXISTS (SELECT 1 from tblUFGUniqueKeyColumns INNER JOIN tblUFGUniqueKeys ON uuq_UniqueKeyName = uqc_UniqueKeyName AND uuq_PrimaryKey=1 AND uqc_TableName=utb_TableName and uqc_ColumnName=ucl_ColumnName) then 1 else 0 end, 
  IsHumanKey = case when uhc_columnName IS NOT NULL then 1 end,
  ucl_TableName, 
  ucl_ColumnName, 
  ucl_SequenceNumber,
  ucl_Visible,
  uct_Title = ISNULL(uct_Title, dbo.fncUFG_NiceTitle(ucl_ColumnName,CASE WHEN @Tablename LIKE 'tblUFG%' THEN 4 ELSE 0 END)),
  ucl_CalculateSQL = CASE WHEN ucl_AsyncRefreshCalcFieldInUpdateMode =1 THEN '/*IMAGine_Async*/' + ucl_CalculateSQL ELSE ucl_CalculateSQL END,
  ucl_IsSharedFK = CASE WHEN FT1.uft_TriggerResultColumn IS NULL then 0 else 1 end,
  ucl_VisibleByLanguage = case when ((@languagedependent=0) OR ((@languagedependent=1) and (ISNULL(udm_DependentLanguage,@Language) = @Language))) then 1 else 0 end,
  uct_ToolTip,
  upn_SequenceNumber = ISNULL(upn_Sequencenumber,9999999),
  upn_NrOfColumns = COALESCE(upn_NrOfColumns,utb_NrOfColumns,4),
  upn_RowSpan,
  upn_PanelNumber,
  udm_DataType=lower(udm_DataType),
  udm_Precision,
  udm_MinimumLength,
  udm_MaximumLength,
  udm_MinimumValue,
  udm_MaximumValue,
  udm_ShowSum,
  udm_ShowAvg,
  udm_ShowStDev,
  udm_ShowStDevP,
  udm_ShowVar,
  udm_ShowVarP,
  udm_BorderValue,
  udm_ColourLow,
  udm_ColourHigh,
  udm_Directory,
  udm_Scale,
  udm_CalendarRepetition,
  udm_StateTransitionTableName, 
  udm_UpperCase,
  udm_SearchPrefixWildcard,
  udm_SearchPostfixWildCard,
  uid_RelativeDirectory,
  uhc_AscendingOrder,
  uhc_SortOrder,
  uhc_ConcatenationChar,
  uhc_SequenceNumber,
  uhc_VisibleInResolve,
  utt_Title = ISNULL(utt_Title,dbo.fncUFG_NiceTitle(ucl_TableName,0)),
  upt_PanelTitle = ISNULL(upt_PanelTitle,'Panel' + CAST(upt_PanelNumber AS varchar(5))),
  utb_NrOfColumns = ISNULL(utb_NrOfColumns,4),
  utb_IconNameOpened,
  utb_ViewType,
  utb_HasHelp = case when exists(select 1 FROM tblUFGHelpText WHERE uhe_TableName = utb_TableName and uhe_Language = @Language and ( (uhe_rolename = '') OR (@isadmin = 1) OR (uhe_rolename IN  (SELECT uir_RoleName FROM tblUFGUsersInRoles where uir_UserName = @UserName and uir_RoleIsPlayed=1)))) then 1 else 0 end,
  utb_HasMenuActions = case when exists(select 1 FROM tblUFGMenuActions inner join tblUFGMenuActionRoles on uma_TableName = uar_TableName where uma_TableName = utb_TableName and ((@isadmin = 1) OR (uar_rolename IN (SELECT uir_RoleName FROM tblUFGUsersInRoles where uir_UserName = @UserName and uir_RoleIsPlayed=1)))) then 1 else 0 end,
  utb_HasHierarchicalGrid = case when exists(select 1 FROM tblUFGHierarchicalGrids where uhg_PublishedForm = @tableName) then 1 else 0 end,
  utb_SelectRowRights = SelectRowRights,
  utb_InsertRowRights = InsertRowRights,
  utb_UpdateRowRights = UpdateRowRights,
  utb_DeleteRowRights = DeleteRowRights,
  utb_CouldHaveReports = case when exists(select 1 FROM tblUFGXMLReports INNER JOIN tblUFGXMLReportRoles on uxr_ReportName = urp_ReportName where urp_BoundTable = utb_TableName and uxr_RoleName in (SELECT uir_RoleName FROM tblUFGUsersInRoles WHERE uir_UserName = @username and uir_RoleIsPlayed=1)) then 1 else 0 end,
  utb_hasFormtriggers = case when exists(select 1 FROM tblUFGFormtriggers where uft_TriggerAction <> 'SharedForeignKey' and uft_PublishedTable = utb_TableName) then 1 else 0 end,
  utb_hasSearchFilters = case when exists(select 1 FROM tblUFGSearchFilters where usf_IsWork =0 and usf_TableName = utb_tablename) then 1 else 0 end,
  ups_Opened = ISNULL(ups_Opened,1),
  utb_Insertsp,
  utb_Updatesp,
  utb_Deletesp,
  utb_Defaultsp,
  ugt_OpenedAsGrid = ISNULL(ugt_OpenedAsGrid,1),
  ure_RegularExpression,
  ure_Errormessage,
  ure_Function,
  b.ufc_ForeignKeyName,
  b.ufc_ReferenceTableName,
  b.ufc_ReferenceColumnName,
  --b.ufc_AdditionalWhere,
	ucr_InsertRight = tmp_InsertRight,
	ucr_UpdateRight = tmp_UpdateRight,
	ucr_DeleteRight = tmp_DeleteRight,
	ucr_SearchRight = tmp_SearchRight,
	ucr_VisibleInGrid = tmp_VisibleInGrid,
  urt_SelectRRDesc = SRRText,
  urt_InsertRRDesc = IRRText,
  urt_UpdateRRDesc = URRText,
  urt_DeleteRRDesc = DRRText,
  upn_ShowConditionColumn,
  upn_ShowConditionColumnValue,
  uop_ShowAuditColumns=ISNULL(uop_ShowAuditColumns,0),
  uop_ShowOperators=ISNULL(uop_ShowOperators,0),
  uop_ShowMultiSelect=ISNULL(uop_ShowMultiSelect,0),
  uop_ShowCount=ISNULL(uop_ShowCount,0),
  udm_AllowedFileTypesOrChars,
  utt_Description,
  utb_RenderAsCheck = CAST(CASE WHEN (ufc_ReferenceTableName IS NOT NULL) THEN (SELECT utb_RenderAsCheck FROM tblUFGTables WHERE utb_TableName = b.ufc_ReferenceTableName) else 0 END AS BIT),
  ucl_CustomValidationRule,
  ucl_CustomValidationMessage,
  udm_DependentLanguage,
  ucl_IsUnique=CAST((CASE WHEN EXISTS(SELECT uqc_ColumnName FROM tblUFGUniqueKeyColumns WHERE uqc_Tablename = ucl_Tablename and uqc_ColumnName = ucl_ColumnName and uqc_UniqueKeyName IN (SELECT uqc_UniqueKeyName FROM tblUFGUniqueKeyColumns WHERE uqc_TableName = ucl_TableName GROUP BY uqc_UniqueKeyName HAVING COUNT(*)=1)) THEN 1 ELSE 0 END) AS BIT),
  upn_Width,
  uct_SubTitle,
  ucl_CSSColumnName,
  ufk_InsertUpdateRendering,
  upn_CssClass,
  upn_ShowConditionNoMatch=CASE WHEN upn_ShowConditionColumn IS NOT NULL THEN upn_ShowConditionNoMatch ELSE '' END,
  upn_WhenEmpty,
  HasLegend=CAST(CASE WHEN EXISTS(SELECT * FROM tblUFGTableLegendItems WHERE utl_TableName = utb_Tablename) THEN 1 ELSE 0 END AS BIT),
  uct_PostFix,
  utb_SoftConstraintProcedureName
FROM 
  tblUFGTables 
  OUTER APPLY fncUFG_GetRowRights(@Username,utb_TableName,@Language)
  LEFT JOIN tblUFGTableTitles on utb_TableName = utt_Tablename and utt_LanguageName = @Language
  LEFT JOIN tblUFGOpenGridTables on utb_TableName = ugt_TableName and ugt_UserName = @Username 
  LEFT JOIN tblUFGOpenSearchPanels ON uop_TableName = utb_TableName AND uop_FKName='' and uop_Username = @username
  INNER JOIN tblUFGColumns on utb_TableName = ucl_TableName     
  INNER JOIN tblUFGDomains ON ucl_DomainName = udm_DomainName
  LEFT JOIN tblUFGColumnTitles on uct_TableName=ucl_TableName and uct_ColumnName=ucl_ColumnName and uct_Languagename = @language
  INNER JOIN 
  (
  SELECT
		tmp_ColumnName = ucl_ColumnName,
		tmp_SelectRight = CAST(CASE WHEN (@Isadmin=1) OR EXISTS(SELECT * FROM tblUFGColumnRights INNER JOIN tblUFGUsersInRoles ON uir_RoleName = ucr_RoleName AND uir_Username = @UserName and uir_RoleIsPlayed=1 WHERE ucr_TableName = ucl_TableName and ucr_ColumnName = ucl_ColumnName AND ucr_SelectRight=1) THEN 1 ELSE 0 END AS BIT),
		tmp_InsertRight = CAST(CASE WHEN (@Isadmin=1) OR EXISTS(SELECT * FROM tblUFGColumnRights INNER JOIN tblUFGUsersInRoles ON uir_RoleName = ucr_RoleName AND uir_Username = @UserName and uir_RoleIsPlayed=1 WHERE ucr_TableName = ucl_TableName and ucr_ColumnName = ucl_ColumnName AND ucr_InsertRight=1) THEN 1 ELSE 0 END AS BIT),
		tmp_UpdateRight = CAST(CASE WHEN (@Isadmin=1) OR EXISTS(SELECT * FROM tblUFGColumnRights INNER JOIN tblUFGUsersInRoles ON uir_RoleName = ucr_RoleName AND uir_Username = @UserName and uir_RoleIsPlayed=1 WHERE ucr_TableName = ucl_TableName and ucr_ColumnName = ucl_ColumnName AND ucr_UpdateRight=1) THEN 1 ELSE 0 END AS BIT),
		tmp_DeleteRight = CAST(CASE WHEN (@Isadmin=1) OR EXISTS(SELECT * FROM tblUFGColumnRights INNER JOIN tblUFGUsersInRoles ON uir_RoleName = ucr_RoleName AND uir_Username = @UserName and uir_RoleIsPlayed=1 WHERE ucr_TableName = ucl_TableName and ucr_ColumnName = ucl_ColumnName AND ucr_DeleteRight=1) THEN 1 ELSE 0 END AS BIT),
		tmp_SearchRight = CAST(CASE WHEN (@Isadmin=1) OR EXISTS(SELECT * FROM tblUFGColumnRights INNER JOIN tblUFGUsersInRoles ON uir_RoleName = ucr_RoleName AND uir_Username = @UserName and uir_RoleIsPlayed=1 WHERE ucr_TableName = ucl_TableName and ucr_ColumnName = ucl_ColumnName AND ucr_SearchRight=1) THEN 1 ELSE 0 END AS BIT),
		tmp_VisibleInGrid = CAST(CASE WHEN (@Isadmin=1) OR EXISTS(SELECT * FROM tblUFGColumnRights INNER JOIN tblUFGUsersInRoles ON uir_RoleName = ucr_RoleName AND uir_Username = @UserName and uir_RoleIsPlayed=1 WHERE ucr_TableName = ucl_TableName and ucr_ColumnName = ucl_ColumnName AND ucr_VisibleInGrid=1) THEN 1 ELSE 0 END AS BIT)
	FROM tblUFGColumns
	WHERE @Tablename = ucl_TableName
	AND 
	CASE WHEN (@Isadmin=1) OR EXISTS(SELECT * FROM tblUFGColumnRights INNER JOIN tblUFGUsersInRoles ON uir_RoleName = ucr_RoleName AND uir_Username = @UserName and uir_RoleIsPlayed=1 WHERE ucr_TableName = ucl_TableName and ucr_ColumnName = ucl_ColumnName AND ucr_SelectRight=1) THEN 1 ELSE 0 END =1
  )
  ColmnRights ON ucl_ColumnName = tmp_ColumnName
  LEFT OUTER JOIN tblUFGPanels ON upn_TableName = ucl_Tablename and upn_PanelNumber = ucl_PanelNumber
  LEFT OUTER JOIN tblUFGPanelTitles ON upn_TableName = upt_TableName and upn_PanelNumber = upt_PanelNumber and upt_Language = @Language
  LEFT OUTER JOIN tblUFGOpenPanels  ON ups_TableName = upn_TableName and ups_PanelNumber = upn_PanelNumber and ups_Username = @Username
  LEFT OUTER JOIN tblUFGHumanKeyColumns ON ucl_TableName=uhc_tableName and ucl_ColumnName=uhc_ColumnName
  LEFT OUTER JOIN tblUFGForeignKeyColumns as b ON ucl_TableName = b.ufc_TableName and ucl_ColumnName = b.ufc_ColumnName
  LEFT OUTER JOIN tblUFGForeignKeys on b.ufc_ForeignKeyName = ufk_ForeignKeyName
  LEFT OUTER JOIN tblUFGFormTriggers FT1 ON FT1.uft_PublishedTable = ucl_TableName and FT1.uft_TriggerResultColumn = ucl_ColumnName and FT1.uft_TriggerAction = 'SharedForeignKey'
  LEFT OUTER JOIN tblUFGRegularExpressions on ure_DomainName = udm_DomainName and ure_LanguageName=@Language 
  LEFT OUTER JOIN tblUFGDirectories ON uid_DirectoryID = udm_Directory
WHERE 
  utb_TableName = @TableName
ORDER BY 
  upn_sequencenumber ASC,
  IsHumanKey DESC,
  ucl_SequenceNumber ASC
GO

ALTER PROCEDURE spUFGGetAllLanguages
WITH ENCRYPTION AS
--IF EXISTS(SELECT 1 FROM tblUFGUsers HAVING COUNT(*)>5) BEGIN RAISERROR('The current IMAGine version licenced to HAN University of Applied Sciences is limited to a maximum of 5 users.',17,1) RETURN END
SET NOCOUNT ON
SELECT ulg_LanguageName, ulg_LanguageIcon FROM tblUFGLanguages
GO